using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

namespace DAL
{
    public class DBHelper
    {
        public string ConnectionString { get; set; }

        public string FixCNN(string connStr, bool pooling)
        {
            string[] aconnStr = connStr.Split(';');
            string sTemp = "";

            for (int i = 0; i < aconnStr.Length; i++)
            {
                if (
                    !aconnStr[i].ToLower().StartsWith("pooling=")
                    && !aconnStr[i].ToLower().StartsWith("min pool size=")
                    && !aconnStr[i].ToLower().StartsWith("max pool size=")
                    && !aconnStr[i].ToLower().StartsWith("connect timeout=")
                    && !aconnStr[i].Equals("")
                    )
                {
                    sTemp += aconnStr[i] + ";";
                }
            }

            if (pooling)
            {
                sTemp += "Pooling=true;Min Pool Size=5;Max Pool Size=15;Connect Timeout=2;Connection Reset = True;Connection Lifetime = 600;";
            }
            else
            {
                sTemp += "Pooling=false;Connect Timeout=45;";
            }
            return sTemp;

        }

        public object FormatInput<T>(T? item) where T : struct
        {
            if (item == null)
            {
                return DBNull.Value;
            }

            return item.Value;
        }

        public object FormatInput(string item)
        {
            if (item == null)
            {
                return DBNull.Value;
            }

            return item;
        }

        public DBHelper()
        {
            //
            // TODO: Add constructor logic here
            //
        }

        public DBHelper(string connStr)
        {
            ConnectionString = connStr;
        }

        public SqlConnection Connection { get; set; }

        public void Open()
        {
            if (ConnectionString == string.Empty)
            {
                throw new Exception("Connection String can not null");
            }
        }

        public void Close()
        {
            CloseConnection(Connection);
        }

        /// <summary>
        /// return an Open SqlConnection
        /// </summary>

        public SqlConnection OpenConnection(string connectionString)
        {
            try
            {
                ConnectionString = connectionString;
                return OpenConnection();
            }
            catch (SqlException myException)
            {
                throw (new Exception(myException.Message));
            }
        }

        /// <summary>
        /// return an Open SqlConnection
        /// </summary>
        public SqlConnection OpenConnection()
        {
            if (ConnectionString == "")
            {
                throw new Exception("Connection String can not null");
            }
            SqlConnection mySqlConnection;

            try
            {
                mySqlConnection = new SqlConnection(FixCNN(ConnectionString, true));
                mySqlConnection.Open();
                return mySqlConnection;
            }
            catch (Exception)
            {
                // De phong truong hop bi max pool thi se fix lai connection string pooling=false
                mySqlConnection = new SqlConnection(FixCNN(ConnectionString, false));
                mySqlConnection.Open();
                return mySqlConnection;
                // throw (new Exception(myException.Message));
            }


        }

        /// <summary>
        /// close an SqlConnection
        /// </summary>
        public void CloseConnection(SqlConnection mySqlConnection)
        {
            try
            {
                if (mySqlConnection != null)
                {
                    if (mySqlConnection.State == ConnectionState.Open)
                    {
                        mySqlConnection.Close();
                    }
                }
            }
            catch (SqlException myException)
            {
                throw (new Exception(myException.Message));
            }
        }

        # region ExecuteScalar
        public object ExecuteScalar(SqlCommand sqlCommand)
        {
            SqlConnection conn = null;
            // ThanhDT add for input null value
            foreach (SqlParameter par in sqlCommand.Parameters)
            {
                if (par.Value == null)
                {
                    par.Value = DBNull.Value;
                }
            }
            try
            {
                if (Connection == null)
                {
                    conn = OpenConnection();
                    sqlCommand.Connection = conn;
                }
                else
                {
                    sqlCommand.Connection = Connection;
                }
                return sqlCommand.ExecuteScalar();
            }
            catch (SqlException myException)
            {
                return -1;
            }
            finally
            {
                if (conn != null)
                {
                    CloseConnection(conn);
                }
            }
        }

        public object ExecuteScalar(SqlCommand sqlCommand, params SqlParameter[] parameters)
        {
            try
            {
                sqlCommand.Parameters.AddRange(parameters);
                return ExecuteScalar(sqlCommand);
            }
            catch (Exception myException)
            {
                return -1;
                throw (new Exception(myException.Message));
            }
        }

        public object ExecuteScalar(string strSql)
        {
            SqlCommand sqlCommand = new SqlCommand(strSql);
            return ExecuteScalar(sqlCommand);
        }

        public object ExecuteScalar(string strSql, params SqlParameter[] parameters)
        {
            SqlCommand sqlCommand = new SqlCommand(strSql);
            return ExecuteScalar(sqlCommand, parameters);
        }

        public object ExecuteScalarSP(string spName)
        {
            SqlCommand sqlCommand = new SqlCommand(spName);
            sqlCommand.CommandType = CommandType.StoredProcedure;
            return ExecuteScalar(sqlCommand);
        }

        public object ExecuteScalarSP(string spName, params SqlParameter[] parameters)
        {
            SqlCommand sqlCommand = new SqlCommand(spName);
            sqlCommand.CommandType = CommandType.StoredProcedure;
            return ExecuteScalar(sqlCommand, parameters);
        }
        #endregion

        # region ExecuteNonQuery
        public int ExecuteNonQuery(SqlCommand sqlCommand)
        {
            SqlConnection conn = null;
            // ThanhDT add for input null value
            foreach (SqlParameter par in sqlCommand.Parameters)
            {
                if (par.Value == null)
                {
                    par.Value = DBNull.Value;
                }
            }
            try
            {
                if (Connection == null)
                {
                    conn = OpenConnection();
                    sqlCommand.Connection = conn;
                }
                else
                {
                    sqlCommand.Connection = Connection;
                }
                return sqlCommand.ExecuteNonQuery();
            }
            catch (SqlException myException)
            {
                throw (new Exception(myException.Message));
            }
            finally
            {
                if (conn != null)
                {
                    CloseConnection(conn);
                }
            }
        }

        public int ExecuteNonQuery(SqlCommand sqlCommand, params SqlParameter[] parameters)
        {
            try
            {
                sqlCommand.Parameters.AddRange(parameters);
                return ExecuteNonQuery(sqlCommand);
            }
            catch (Exception myException)
            {
                throw (new Exception(myException.Message));
            }
        }

        public int ExecuteNonQuery(string strSql)
        {
            SqlCommand sqlCommand = new SqlCommand(strSql);
            return ExecuteNonQuery(sqlCommand);
        }

        public int ExecuteNonQuery(string strSql, params SqlParameter[] parameters)
        {
            SqlCommand sqlCommand = new SqlCommand(strSql);
            return ExecuteNonQuery(sqlCommand, parameters);
        }

        public int ExecuteNonQuerySP(string spName, params SqlParameter[] parameters)
        {
            SqlCommand sqlCommand = new SqlCommand(spName);
            sqlCommand.CommandType = CommandType.StoredProcedure;
            return ExecuteNonQuery(sqlCommand, parameters);
        }
        #endregion

        #region ExecuteSP

        /// <summary>
        /// Execute stored procedure with return value
        /// </summary>
        /// <param name="spName"></param>
        /// <returns>Get return value from stored</returns>
        public int ExecuteInt32SP(string spName)
        {
            SqlCommand sqlCommand = new SqlCommand(spName);
            sqlCommand.CommandType = CommandType.StoredProcedure;
            SqlParameter returnValue = new SqlParameter("@ReturnValue", SqlDbType.Int);
            returnValue.Direction = ParameterDirection.ReturnValue;
            sqlCommand.Parameters.Add(returnValue);
            ExecuteNonQuery(sqlCommand);
            return (int)sqlCommand.Parameters["@ReturnValue"].Value;
        }

        /// <summary>
        /// Execute sql command with return value
        /// </summary>
        /// <param name="command"></param>
        /// <returns>Get return value from stored</returns>
        public int ExecuteInt32Query(SqlCommand command)
        {
            SqlParameter returnValue = new SqlParameter("@ReturnValue", SqlDbType.Int);
            returnValue.Direction = ParameterDirection.ReturnValue;
            command.Parameters.Add(returnValue);
            ExecuteNonQuery(command);
            return (int)command.Parameters["@ReturnValue"].Value;
        }

        /// <summary>
        /// Execute stored procedure with return value
        /// </summary>
        /// <param name="spName"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public int ExecuteInt32SP(string spName, params SqlParameter[] parameters)
        {
            SqlCommand sqlCommand = new SqlCommand(spName);
            sqlCommand.CommandType = CommandType.StoredProcedure;
            SqlParameter returnValue = new SqlParameter("@ReturnValue", SqlDbType.Int);
            returnValue.Direction = ParameterDirection.ReturnValue;
            sqlCommand.Parameters.Add(returnValue);
            ExecuteNonQuery(sqlCommand, parameters);
            return (int)sqlCommand.Parameters["@ReturnValue"].Value;
        }

        /// <summary>
        /// Execute stored procedure with return value
        /// </summary>
        /// <param name="spName"></param>
        /// <returns>Get return value from stored</returns>
        public object ExecuteSP(string spName)
        {
            SqlCommand sqlCommand = new SqlCommand(spName);
            sqlCommand.CommandType = CommandType.StoredProcedure;
            SqlParameter returnValue = new SqlParameter("@ReturnValue", SqlDbType.Int);
            returnValue.Direction = ParameterDirection.ReturnValue;
            sqlCommand.Parameters.Add(returnValue);
            ExecuteNonQuery(sqlCommand);
            return sqlCommand.Parameters["@ReturnValue"].Value;
        }

        /// <summary>
        /// Execute sql command with return value
        /// </summary>
        /// <param name="command"></param>
        /// <returns>Get return value from stored</returns>
        public object ExecuteQuery(SqlCommand command)
        {
            SqlParameter returnValue = new SqlParameter("@ReturnValue", SqlDbType.Int);
            returnValue.Direction = ParameterDirection.ReturnValue;
            command.Parameters.Add(returnValue);
            ExecuteNonQuery(command);
            return command.Parameters["@ReturnValue"].Value;
        }

        /// <summary>
        /// Execute stored procedure with return value
        /// </summary>
        /// <param name="spName"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public object ExecuteSP(string spName, params SqlParameter[] parameters)
        {
            SqlCommand sqlCommand = new SqlCommand(spName);
            sqlCommand.CommandType = CommandType.StoredProcedure;
            SqlParameter returnValue = new SqlParameter("@ReturnValue", SqlDbType.Int);
            returnValue.Direction = ParameterDirection.ReturnValue;
            sqlCommand.Parameters.Add(returnValue);
            ExecuteNonQuery(sqlCommand, parameters);
            return sqlCommand.Parameters["@ReturnValue"].Value;
        }
        #endregion

        /// <summary>
        /// Execute a sql command
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sqlCommand"></param>
        /// <returns></returns>
        public List<T> ExecuteSqlList<T>(SqlCommand sqlCommand)
        {
            SqlConnection conn = null;
            // ThanhDT add for input null value
            foreach (SqlParameter par in sqlCommand.Parameters)
            {
                if (par.Value == null)
                {
                    par.Value = DBNull.Value;
                }
            }
            try
            {
                if (Connection == null)
                {
                    conn = OpenConnection();
                    sqlCommand.Connection = conn;
                }
                else
                {
                    sqlCommand.Connection = Connection;
                }
                SqlDataReader dataReader = sqlCommand.ExecuteReader();
                if (dataReader == null || dataReader.FieldCount == 0)
                    return null;

                var recordList = new List<T>();

                var builder = DynamicBuilder<T>.CreateBuilder(dataReader);

                while (dataReader.Read())
                {
                    var record = builder.Build(dataReader);
                    recordList.Add(record);
                }
                dataReader.Close();
                return recordList;
            }
            catch (SqlException myException)
            {
                throw (new Exception(myException.Message));
            }
            finally
            {
                if (conn != null)
                {
                    CloseConnection(conn);
                }
            }
        }

        /// <summary>
        /// Execute a sql command with return total
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sqlCommand"></param>
        /// <param name="totalRows"></param>
        /// <returns></returns>
        public List<T> ExecuteSqlList<T>(SqlCommand sqlCommand, out int totalRows)
        {
            SqlConnection conn = null;
            try
            {
                if (Connection == null)
                {
                    conn = OpenConnection();
                    sqlCommand.Connection = conn;
                }
                else
                {
                    sqlCommand.Connection = Connection;
                }
                totalRows = 0;
                SqlDataReader dataReader = sqlCommand.ExecuteReader();
                if (dataReader == null || dataReader.FieldCount == 0)
                {
                    return null;
                }

                var dataList = new List<T>();
                var builder = DynamicBuilder<T>.CreateBuilder(dataReader);
                while (dataReader.Read())
                {
                    var record = builder.Build(dataReader);
                    dataList.Add(record);
                }
                if (dataReader.NextResult())
                {
                    if (dataReader.Read())
                    {
                        totalRows = (int)dataReader["TotalRowCount"];
                    }
                }
                dataReader.Close();

                return dataList;
            }
            catch (SqlException myException)
            {
                throw (new Exception(myException.Message));
            }
            finally
            {
                if (conn != null)
                {
                    CloseConnection(conn);
                }
            }
        }

        /// <summary>
        /// Execute a sql command with parameters
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sqlCommand"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public List<T> ExecuteSqlList<T>(SqlCommand sqlCommand, params SqlParameter[] parameters)
        {
            try
            {
                sqlCommand.Parameters.AddRange(parameters);
                return ExecuteSqlList<T>(sqlCommand);
            }
            catch (Exception myException)
            {
                throw (new Exception(myException.Message));
            }
        }

        /// <summary>
        /// Execute a sql query
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="strSql"></param>
        /// <returns></returns>
        public List<T> ExecuteSqlList<T>(string strSql)
        {
            SqlCommand sqlCommand = new SqlCommand(strSql);
            return ExecuteSqlList<T>(sqlCommand);
        }

        /// <summary>
        /// Execute a sql store procedure
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="storeName"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public List<T> ExecuteSPList<T>(string storeName)
        {
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandText = storeName;
            sqlCommand.CommandType = CommandType.StoredProcedure;

            return ExecuteSqlList<T>(sqlCommand);
        }

        /// <summary>
        /// Execute a sql store procedure with parametes
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="storeName"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public List<T> ExecuteSPList<T>(string storeName, params SqlParameter[] parameters)
        {
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandText = storeName;
            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.Parameters.AddRange(parameters);

            return ExecuteSqlList<T>(sqlCommand);
        }
    }
}